<?php

/**
 * 产品详细信息
 * @copyright   Copyright (c) 2006 - 2010 DZH 友拓软件友拓通
 * @category    统计
 * @package   Model
 * @author     庄敏学
 * @version  2.1,2012-07-22
 */

class StatProductPublicModel extends RelationCommonModel  {
	
	protected $tableName='product';
	
	public function __construct(){
		parent::__construct();
		import('ORG.Util.Page');
	}
	
	
	/**
	 * 取查看产品时的明细信息
	 * @param int $id 产品ID
	 * @return array
	 */
	public  function getBasicInfo($id) {	
		$rs 		= M('Product')->field('*,cube_long*cube_high*cube_wide cube')->find($id);// 取主表信息
		$detail 	= M('ProductDetail')->where('product_id='.$id)->formatFindAll(array('key'=>'properties_id'));// 取明细信息
		// 产品图片信息
		$rs['pics'] = D('Gallery')->getAry($id,1);		

		// 取在用的产品属性信息
		$userInfo	= getUser();
		$role_type	= $userInfo['role_type'];
		$role_type_where = ($role_type ==1||$_SESSION[C('ADMIN_AUTH_KEY')]==true) ? " " : " and role_type in (1,".$role_type.")";
		if(LANG_SET=='cn'){
			$properties_name = 'properties_name';
			$pv_name = 'pv_name';
		}else {
			$properties_name = 'properties_name_fr as properties_name';
			$pv_name = 'pv_name_fr as pv_name';
		}
		$properties 	= M('Properties')->field('id as properties_id,properties_no,'.$properties_name.',properties_type')->where('to_hide=1'.$role_type_where)->formatFindAll(array('key'=>'properties_id'));
		$_cache_proper 	= M('PropertiesValue')->field('id,'.$pv_name)->formatFindAll(array('key'=>'id'));
		foreach ($properties as $_id => &$var) {
			$var['product_id'] 	= $detail[$_id]['product_id'];
			$var['value'] 		= $detail[$_id]['value'];
			switch ($var['properties_type']) {
				case 1:// 自定义输入
					$var['properties_value'] = $var['value'];
				break;
				case 2:// 单选框
					$var['properties_value'] = $_cache_proper[$var['value']]['pv_name'];
				break;
				case 3:// 多选框
					if (strpos($var['value'],',')) {
						$temp = @explode(',',$var['value']);
						foreach ((array)$temp as $properties_value_id) {
							$v_ary[] = $_cache_proper[$properties_value_id]['pv_name'];
						}
						$var['properties_value'] = implode('，',$v_ary);
					} else {
						$var['properties_value'] = $_cache_proper[$var['value']]['pv_name'];
					}
				break;
			}
		}
		!empty($properties) && $rs['detail'] = $properties;
		// 取颜色信息
		if (C('product_color')==1) {
			$temp = M('ProductColor')->where('product_id='.$id)->field('color_id')->select();
			if (!empty($temp)) {
				$dd_color = S('color');
				foreach ($temp as $value) {
					$rs['color'][] = $dd_color[$value['color_id']]['color_name'];
				}
				$rs['color'] = implode('，',$rs['color']);
			}
		}
		// 取尺码信息
		if (C('product_size')==1) {
			$temp = M('ProductSize')->where('product_id='.$id)->field('size_id')->select();
			if (!empty($temp)) {
				$dd_size = S('size');
				foreach ($temp as $value) {
					$rs['size'][] = $dd_size[$value['size_id']]['size_name'];
				}
				$rs['size'] = implode('，',$rs['size']);
			}
		}		
		return _formatArray($rs);
	}
	
	/// 获取经营异动信息
	public function getRunInfo($id) {
		
		// 获取订货总数量
		$sql 	= 'select sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn from order_details where product_id='.$id.' group by product_id';
		$orders	= $this->db->query($sql);
		// 获取装柜总数量
		$sql 	= 'select sum(quantity* capability* dozen) as quantity,sum(quantity) sum_qn from load_container_details where product_id='.$id.' group by product_id';
		$load	= $this->db->query($sql);
		$temp['load_quantity']		= floatval($load[0]['quantity']);
		$temp['load_sum_qn']		= floatval($load[0]['sum_qn']);
		$temp['unload_quantity'] 	= $orders[0]['quantity'] - $temp['load_quantity'];
		$temp['unload_sum_qn']		= floatval($orders[0]['sum_qn'])-floatval($load[0]['sum_qn']);
		$temp['unstock_quantity']	= floatval($temp['load_quantity']);
		$temp['unstock_sum_qn']		= floatval($temp['load_sum_qn']);		
		// 取调整数量
		$sql 		= "select 
						abs(sum(if(c.quantity>0,c.quantity*c.capability*c.dozen,0)))  as adjust_in_quantity,abs(sum(if(c.quantity>0,c.quantity,0))) in_quantity,
					   	abs(sum(if(c.quantity<0,c.quantity*c.capability*c.dozen,0)))  as adjust_re_quantity,abs(sum(if(c.quantity<0,c.quantity,0))) re_quantity
						from adjust b inner join adjust_detail c on  b.id=c.adjust_id 
						where product_id=".$id." group by product_id";		
		$adjust 	= $this->db->query($sql);
		// 取盈亏数量
		$sql 		= "select 
						abs(sum(if(stocktake_quantity-storage_quantity>0,(stocktake_quantity-storage_quantity)*c.capability*c.dozen,0)))  as adjust_in_quantity,
						abs(sum(if(stocktake_quantity-storage_quantity>0,(stocktake_quantity-storage_quantity),0)))  as in_quantity,
					   	abs(sum(if(stocktake_quantity-storage_quantity<0,(stocktake_quantity-storage_quantity)*c.capability*c.dozen,0)))  as adjust_re_quantity,
					   	abs(sum(if(stocktake_quantity-storage_quantity<0,(stocktake_quantity-storage_quantity),0)))  as re_quantity
						from profitandloss b inner join profitandloss_detail c on  b.id=c.profitandloss_id 
						where state=2 and product_id=".$id." group by product_id";
		$prof		= $this->db->query($sql);
		// 取期初数量		
		$sql 		= "select sum(c.quantity*c.capability*c.dozen)  as quantity,sum(c.quantity) sum_qn	
						from init_storage b inner join init_storage_detail c on  b.id=c.init_storage_id 
						where product_id=".$id." group by product_id";
		$init 		= $this->db->query($sql);	
		// 取进货数量		
		$sql 		= "select sum(c.quantity*c.capability*c.dozen)  as quantity	,sum(c.quantity) sum_qn
						from instock b inner join instock_detail c on  b.id=c.instock_id 
						where product_id=".$id." group by product_id";
		$instock 	= $this->db->query($sql);		
		// 取销售数量		
		$sql 		= "select sum(c.quantity*c.capability*c.dozen)  as quantity	,sum(c.quantity) sum_qn
						from sale_order b inner join sale_order_detail c on  b.id=c.sale_order_id 
						where product_id=".$id." group by product_id";
		$sale 		= $this->db->query($sql);	
		// 取退货数量
		$sql 		= "select sum(if(c.is_use=1,c.quantity*c.capability*c.dozen,0))  as use_quantity,sum(if(c.is_use=1,c.quantity,0))  as use_qn,
					   	sum(if(c.is_use=2,c.quantity*c.capability*c.dozen,0))  as no_use_quantity,sum(if(c.is_use=2,c.quantity,0))  as no_use_qn
						from return_sale_order b inner join return_sale_order_detail c on  b.id=c.return_sale_order_id 
						where product_id=".$id." group by product_id";
		$no_use 	= $this->db->query($sql);
		// 取发货数量
		$sql 		= "select sum(c.quantity*c.capability*c.dozen)  as quantity,sum(c.quantity) sum_qn	
						from delivery b inner join delivery_detail c on  b.id=c.delivery_id 
						where product_id=".$id." group by product_id";
		$delivery 	= $this->db->query($sql);		
		$list['init_quantity']		= floatval($init[0]['quantity']);
		$list['init_sum_qn']		= floatval($init[0]['sum_qn']);
		$list['stock_quantity']		= floatval($instock[0]['quantity']);	
		$list['stock_sum_qn']		= floatval($instock[0]['sum_qn']);
		$list['unstock_quantity']	= floatval($temp['load_quantity'] - $list['stock_quantity']);
		$list['unstock_sum_qn']		= floatval($temp['load_sum_qn']-$list['stock_sum_qn']);
		$list['no_use_quantity']	= floatval($no_use[0]['no_use_quantity']);
		$list['no_use_qn']			= floatval($no_use[0]['no_use_qn']);
		$list['use_quantity']		= floatval($no_use[0]['use_quantity']);	
		$list['use_qn']				= floatval($no_use[0]['use_qn']);	
		$list['delivery_quantity']	= floatval($delivery[0]['quantity']);
		$list['delivery_sum_qn']	= floatval($delivery[0]['sum_qn']);
		$list['sale_quantity']		= floatval($sale[0]['quantity']);
		$list['sale_sum_qn']		= floatval($sale[0]['sum_qn']);
		$list['adjust_in_quantity']= floatval($adjust[0]['adjust_in_quantity'] + $prof[0]['adjust_in_quantity']);
		$list['adjust_in_sum_qn']		= floatval($adjust[0]['in_quantity'] + $prof[0]['in_quantity'] );
		$list['adjust_re_quantity']= floatval($adjust[0]['adjust_re_quantity'] + $prof[0]['adjust_re_quantity']);	
		$list['adjust_re_sum_qn']	= floatval($adjust[0]['re_quantity'] + $prof[0]['re_quantity']);					
		return (array_merge($temp, (array)$list));
	}
	
	/// 取库存信息
	public function getStorageInfo($id) {
		
		$group = 'color_id,size_id,mantissa';
		if (C('STORAGE_FORMAT') == 1) {
			$field = ',quantity as storage_format';
		} else if (C('STORAGE_FORMAT') == 2){
			$field = ', CONCAT_WS("*", capability) as storage_format';
			$title = '('.L('storage_format_ab').')';
			$group.= ',capability,mantissa';
		}  else if (C('STORAGE_FORMAT') == 3){
			$field = ', CONCAT_WS("*", capability,dozen) as storage_format';
			$title = '('.L('storage_format_abc').')';
			$group.= ',capability,dozen,mantissa';
		} 
		if (!C('STORAGE_ZERO')) {
				$sale_group_where =  ' having(sum(all_sale_storage)) <> 0';
				$real_group_where =  ' having(sum(quantity)) <> 0';
		}		
		if (C('loadContainer.sale_storage')==1) {
			$all_sale_storage_field 	= ' 0 as all_sale_storage';
			$sale_storage_field 		= 'sum(sale_storage-onroad_storage) as sale_storage';
			// 取可销售库存信息
			$sql 	= "select  product_id,color_id,size_id,storage_format,mantissa,sum(quantity) sum_qn,
						  ".$sale_storage_field.",sum(all_sale_storage) as all_sale_storage,sum(onroad_storage) as onroad_storage
						  from (
							select product_id,color_id,size_id,capability,dozen,sum(quantity) quantity,	
							sum(quantity)*capability*dozen AS all_sale_storage,						
							sum(quantity)*capability*dozen AS sale_storage,mantissa,
							0 as onroad_storage".$field." 							
							from sale_storage
							where product_id=".$id.' group by '.$group.'							
							union all
							select product_id,color_id,size_id,capability,dozen,0 quantity,
							'.$all_sale_storage_field.' ,
							0 as sale_storage,mantissa,sum(quantity*capability*dozen) as onroad_storage '.$field.'							 
						 	from load_container a, load_container_details b where  a.id=b.load_container_id 
						  	and b.product_id='.$id.' and a.load_state=1
						  	group by '.$group.'
						  ) as temp group by '.$group.$sale_group_where.' order by color_id,size_id,mantissa';	
		}else {
			$all_sale_storage_field = ' sum(quantity*capability*dozen)  as all_sale_storage';
			$sale_storage_field 		= 'sum(sale_storage) as sale_storage';
			// 取可销售库存信息
			$sql 	= "select  product_id,color_id,size_id,storage_format,mantissa,sum(quantity) sum_qn,
						  ".$sale_storage_field.",sum(all_sale_storage) as all_sale_storage,sum(onroad_storage) as onroad_storage
						  from (
							select product_id,color_id,size_id,capability,dozen,sum(quantity) quantity,	
							sum(quantity)*capability*dozen AS all_sale_storage,						
							sum(quantity)*capability*dozen AS sale_storage,mantissa,
							0 as onroad_storage".$field." 							
							from sale_storage
							where product_id=".$id.' group by '.$group.'						
						  ) as temp group by '.$group.$sale_group_where.' order by color_id,size_id,mantissa';	
		}			
		$sale_storage 	= _formatList($this->db->query($sql));
		foreach($sale_storage['list'] as $key=>$val){
			$sale_storage['total']['sum_qn'] += $val['sum_qn'];
			if($val['mantissa']==2){
				$sale_storage['list'][$key]['mantissa_desc']	= '√';
			}
		}
		// 取实际库存
		$sql 			= " select *,sum(quantity) sum_qn,sum(quantity)*capability*dozen AS storage ".$field." 
							from storage where product_id=".$id.' group by warehouse_id,'.$group.$real_group_where.'
							order by color_id,size_id,mantissa';
		$storage 		= _formatList($this->db->query($sql));	
		foreach($storage['list'] as $key=>$val){
			$storage['total']['sum_qn']	+= $val['sum_qn'];
			$storage['total']['storage']+= $val['storage'];
			if($val['mantissa']==2){
				$storage['list'][$key]['mantissa_desc']	= '√';
			}
		}
		return array('sale_storage' => $sale_storage, 'storage' => $storage, 'title' => $title);
	}
	
	/// 获取明细数据
	public function getDetail() {
		$product	= S('product');
		$product	= $product[intval($_GET['id'])];
		if ($_GET['type']) {
			$fun	= 'get'.ucfirst(trim($_GET['type']));
			$list = $this->$fun(intval($_GET['id']));
			$list['product']	= $product;
			return $list;
		}
	}
	
	
	/// 订货明细
	public function getOrder($id) {
	
		$sql = "select count(*) as count,sum(unload_quantity) as unload_quantity, sum(money) as money,sum(quantity) quantity,sum(sum_qn) sum_qn,
					sum(load_capability) load_capability,sum(load_quantity) load_quantity from (
					select sum(quantity*capability*dozen-load_quantity) as unload_quantity,sum(quantity*capability*dozen * price) as money,sum(quantity) sum_qn,
					sum(quantity*capability*dozen) quantity,sum(load_capability) load_capability,sum(load_quantity) load_quantity
					from orders a inner join order_details b on a.id=b.orders_id 
					where b.product_id=".$id." 
					group by a.id
				) as temp group by 1=1";
		$count 	= M()->cache()->query($sql);
		$count	= reset($count);		
		if (!$count) return ;
		$p 		= new Page($count['count']);
		$page 	= $p->show();
		// 分页信息
		$limit 	= ' limit '.$p->firstRow . ','.$p->listRows;
		$sql = "select *,a.id as m_id,sum(quantity*capability*dozen) as sum_qn,sum(quantity*capability*dozen*price) as money,sum(quantity) quantity,
				sum(load_quantity) as load_quantity,sum(quantity*capability*dozen-load_quantity) as unload_quantity,sum(load_capability) load_capability
				from orders a inner join order_details b on a.id=b.orders_id where b.product_id=".$id.' 
				group by a.id order by a.order_no '.$limit;
		$list= $this->db->query($sql);		
		$list 									= _formatList($list);
		foreach ($list['list'] as &$value) {
			unset($value['create_time'],$value['update_time'],$value['audit_date']);
			$value['view_url'] 					= U('/Orders/view/id/'.$value['m_id']);
			$list['total']['sum_qn']			+= $value['sum_qn'];
			$list['total']['load_capability']	+= $value['load_capability'];
		}
		$list['total']['dml_load_capability']	= moneyFormat($list['total']['load_capability'],0,0);
		$list['total']['dml_sum_qn']			= moneyFormat($list['total']['sum_qn'],0,0);
		$list['total']['product_no']			= $list['list'][0]['product_no'];	
		$list['total']['product_name']			= $list['list'][0]['product_name'];	
		$list['total']['all_quantity']			= moneyFormat($count['quantity'],0,0);
		$list['total']['all_sum_qn']			= moneyFormat($count['sum_qn'],0,0);
		$list['total']['all_load_capability']	= moneyFormat($count['load_capability'],0,0);
		$list['total']['all_load_quantity']		= moneyFormat($count['load_quantity'],0,0);
		$list['total']['all_unload_quantity']  	= moneyFormat($count['unload_quantity'], 0, 0);
		$list['total']['all_money']  			= moneyFormat($count['money'], 0, C('MONEY_LENGTH'));
		$list['page']							= $page;
		
		return $list;
	}
	
	/// 装柜明细
	public function getLoad($id) {
	
		$sql = "select count(*) as count,sum(quantity) as quantity,sum(money) as money,sum(sum_qn) sum_qn from (
					select sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
					sum(quantity*capability*dozen*price) as money
					from load_container a inner join load_container_details b on a.id=b.load_container_id 
					where b.product_id=".$id." 
					group by a.id 
				) as temp group by 1=1";
		$count 	= M()->cache()->query($sql);
		if (!$count) return ;
		$p 		= new Page($count[0]['count']);
		$page 	= $p->show();
		// 分页信息
		$limit 	= ' limit '.$p->firstRow . ','.$p->listRows;
		$sql = "select *,a.id as m_id,sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
				sum(quantity*capability*dozen*price) as money
				from load_container a inner join load_container_details b on a.id=b.load_container_id
				where b.product_id=".$id.' 
				group by a.id order by a.load_container_no '.$limit;
		$list= $this->db->query($sql);		
		// 取入库数量
		$sql = "select sum(quantity*capability*dozen) as quantity,load_container_id
				from instock a inner join instock_detail b on a.id=b.instock_id 
				where b.product_id=".$id." group by load_container_id";
		$tmp	 = $this->db->query($sql);
		foreach((array)$tmp as $val){
			$instock[$val['load_container_id']]	= $val['quantity'];
		}
//		$instock = _formatArray($this->db->query($sql),'load_container_id','quantity');
		$instock_all = array_sum((array)$instock);
		foreach ($list as &$value) {
			unset($value['create_time'],$value['update_time'],$value['audit_date']);
			$value['view_url'] 					= U('/LoadContainer/view/id/'.$value['m_id']);
			$value['unstock_quantity']			= $value['quantity'] - $instock[$value['m_id']];
			$total['sum_qn']								+= $value['sum_qn'];
		}
		$total									= _formatArray($total);		
		$list 									= _formatList($list);
		$list['total']['product_no']			= $list['list'][0]['product_no'];	
		$list['total']['product_name']			= $list['list'][0]['product_name'];	
		$list['total']['all_unstock_quantity']  = moneyFormat($count[0]['quantity']-$instock_all, 0, 0);
		$list['total']['all_money']  			= moneyFormat($count[0]['money'], 0, C('MONEY_LENGTH'));
		$list['total']['all_quantity']			= moneyFormat($count[0]['quantity'],0,0);
		$list['total']['all_sum_qn']			= moneyFormat($count[0]['sum_qn'],0,0);
		$list['total']							= array_merge($list['total'],$total);
		$list['page']							= $page;
		return $list;
	}
	
	/// 进货明细
	public function getInstock($id) {
		$sql = "select count(1) as count,sum(quantity) as quantity,sum(money) as money,sum(sum_qn) sum_qn from (
					select sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
					sum(quantity*capability*dozen*price) as money
					from instock a inner join instock_detail b on a.id=b.instock_id 
					where b.product_id=".$id." 
					group by a.id 
				) as temp group by 1=1";
		$count 	= M()->cache()->query($sql);
		$count	= reset($count);		
		if (!$count) return ;
		$p 		= new Page($count['count']);
		$page 	= $p->show();
		// 分页信息
		$limit 	= ' limit '.$p->firstRow . ','.$p->listRows;		
		// 取入库数量
		$sql = "select instock_no,container_no,real_arrive_date,a.id as instock_id,a.id as m_id, sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
				sum(quantity*capability*dozen*price) as money, 
				sum(quantity*capability*dozen*price)/sum(quantity*capability*dozen) as avg_price
				from instock a inner join instock_detail b on a.id=b.instock_id 
				where b.product_id=".$id." group by load_container_id,a.id
				order by a.instock_no ".$limit;
		$list = $this->db->query($sql);
		$list 							= _formatList($list);
		foreach($list['list'] as $key=>$val){
			$list['total']['sum_qn']	+= $val['sum_qn'];
		}
		$list['total']['product_no']	= $list['list'][0]['product_no'];	
		$list['total']['product_name']	= $list['list'][0]['product_name'];	
		$list['total']['dml_sum_qn']	= moneyFormat($list['total']['sum_qn'],0,0);
		$list['total']['all_quantity']  = moneyFormat($count['quantity'], 0, 0);
		$list['total']['all_money']  	= moneyFormat($count['money'], 0, C('MONEY_LENGTH'));
		$list['total']['all_sum_qn']	= moneyFormat($count['sum_qn'],0,0);
		$list['page']					= $page;
		return $list;
	}
	
	/// 销售明细
	public function getSale($id) {
		$sql = "select count(*) as count,sum(quantity) as quantity,sum(money) as money,sum(sum_qn) sum_qn from (
					select sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
					sum(quantity*capability*dozen*price*discount) as money
					from sale_order a inner join sale_order_detail b on a.id=b.sale_order_id 
					where b.product_id=".$id." 
					group by a.id 
				) as temp group by 1=1";
		$count 	= M()->cache()->query($sql);			
		$count	= reset($count);
		if (!$count) return ;		
		$p 		= new Page($count['count']);
		$page 	= $p->show();
		// 分页信息
		$limit 	= ' limit '.$p->firstRow . ','.$p->listRows;		
		$sql	= "select *,a.id as m_id,sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
					sum(quantity*capability*dozen*price*discount) as money, 
					sum(quantity*capability*dozen*price*discount)/sum(quantity*capability*dozen) as avg_price
					from sale_order a inner join sale_order_detail b on a.id=b.sale_order_id 
					where b.product_id=".$id." 
					group by a.id 
					order by a.sale_order_no ".$limit;
		$list = $this->db->query($sql);		
		foreach ($list as &$value) {
			unset($value['create_time'],$value['update_time'],$value['audit_date']);
			$value['sale_type']					= $value['order_type'] == 1 ? L('sale') : L('return_sale_type_exchange');
			$value['view_url'] 					= U('/SaleOrder/view/id/'.$value['m_id']);
			$curr_money[$value['currency_id']] += $value['money'];			
			$total['sum_qn']					+=$value['sum_qn'];
		}	
		
		$list 							= _formatList($list);		
		$list['total']					= array_merge($list['total'],_formatArray($total));
		$list['total']['product_no']	= $list['list'][0]['product_no'];	
		$list['total']['product_name']	= $list['list'][0]['product_name'];	
		// 总合计
		$sql	= " select sum(quantity*capability*dozen*price*discount) as money, currency_id ,sum(quantity) sum_qn
					from sale_order a inner join sale_order_detail b on a.id=b.sale_order_id 
					where b.product_id=".$id." 
					group by currency_id ";
		$total	= $this->db->query($sql);	
		$currency	= S('currency');
		foreach ($total as $t_list) {
			$all_money.=$currency[$t_list['currency_id']]['currency_no'].'：'.moneyFormat($t_list['money'], 0, C('MONEY_LENGTH')).'<br>';			
		}
		foreach ($curr_money as $k => $m) {
			$total_money.=$currency[$k]['currency_no'].'：'.moneyFormat($m, 0, C('MONEY_LENGTH')).'<br>';			
		}
		$list['total']['all_sum_qn']	= moneyFormat($count['sum_qn'],0,0);
		$list['total']['all_quantity']  = moneyFormat($count['quantity'], 0, 0);
		$list['total']['all_money']  	= $all_money;
		$list['total']['total_money']  	= $total_money;
		$list['page']					= $page;
		return $list;
	}
	
	/// 发货明细
	public function getDelivery($id) {
		$sql = "select count(*) as count,sum(quantity) as quantity,sum(money) as money,sum(sum_qn) sum_qn from (
					select sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
					sum(quantity*capability*dozen*price*discount) as money
					from delivery a inner join delivery_detail b on a.id=b.delivery_id 					
					where b.product_id=".$id." 
					group by a.id 
				) as temp group by 1=1";
		$count 	= M()->cache()->query($sql);			
		$count	= reset($count);
		if (!$count) return ;
		$p 		= new Page($count['count']);
		$page 	= $p->show();
		// 分页信息
		$limit 	= ' limit '.$p->firstRow . ','.$p->listRows;		
		$sql	= "select a.*,b.*,c.currency_id,a.id as m_id,sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
					sum(quantity*capability*dozen*price*discount) as money, 
					sum(quantity*capability*dozen*price*discount)/sum(quantity*capability*dozen) as avg_price
					from delivery a inner join delivery_detail b on a.id=b.delivery_id 
					left join sale_order c on  c.id=b.sale_order_id
					where b.product_id=".$id." 
					group by a.id 
					order by a.delivery_no ".$limit;
		$list = $this->db->query($sql);
		foreach ($list as &$value) {
			unset($value['create_time'],$value['update_time']);
			$value['view_url'] 			= U('/Delivery/view/id/'.$value['m_id']);
			$curr_money[$value['currency_id']] += $value['money'];	
			$total['sum_qn']			+= $value['sum_qn'];				
		}	
		$list 							= _formatList($list);
		$list['total']					= array_merge($list['total'],_formatArray($total));
		$list['total']['product_no']	= $list['list'][0]['product_no'];	
		$list['total']['product_name']	= $list['list'][0]['product_name'];	
		// 总合计
		$sql	= " select sum(quantity*capability*dozen*price*discount) as money, currency_id ,sum(quantity) sum_qn
					from delivery a inner join delivery_detail b on a.id=b.delivery_id 
					left join sale_order c on  c.id=b.sale_order_id
					where b.product_id=".$id." 
					group by currency_id ";
		$total	= $this->db->query($sql);	
		$currency	= S('currency');
		foreach ($total as $t_list) {
			$all_money.=$currency[$t_list['currency_id']]['currency_no'].'：'.moneyFormat($t_list['money'], 0, C('MONEY_LENGTH')).'<br>';			
		}
		foreach ($curr_money as $k => $m) {
			$total_money.=$currency[$k]['currency_no'].'：'.moneyFormat($m, 0, C('MONEY_LENGTH')).'<br>';			
		}
		$list['total']['all_sum_qn']	= moneyFormat($count['sum_qn'],0,0);
		$list['total']['all_quantity']  = moneyFormat($count['quantity'], 0, 0);
		$list['total']['all_money']  	= $all_money;
		$list['total']['total_money']  	= $total_money;
		$list['page']					= $page;
		return $list;
	}
	
	/// 发货明细
	public function getReturn($id) {
		$title = $_GET['is_use'] == 1 ? L('usefull') : L('unusefull');
		$sql = "select count(*) as count,sum(quantity) as quantity, sum(money) as money,sum(sum_qn) sum_qn from (
					select sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
					sum(quantity*capability*dozen*discount) as money 
					from  return_sale_order a inner join  return_sale_order_detail b on a.id=b.return_sale_order_id 
					where b.product_id=".$id." and b.is_use=".$_GET['is_use']."
					group by b.id 
				) as temp group by 1=1";
		$count 	= M()->cache()->query($sql);
		$count	= reset($count);		
		if (!$count) {
			$list['total']['title'] = $title;
			return $list;
		}
		$p 		= new Page($count['count']);
		$page 	= $p->show();
		// 分页信息
		$limit 	= ' limit '.$p->firstRow . ','.$p->listRows;		
		$sql	= "select *,a.id as m_id,sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
					sum(quantity*capability*dozen*price*discount) as money, 
					sum(quantity*capability*dozen*price*discount)/sum(quantity*capability*dozen) as avg_price
					from  return_sale_order a inner join  return_sale_order_detail b on a.id=b.return_sale_order_id 
					where b.product_id=".$id." and b.is_use=".$_GET['is_use']."
					group by b.id 
					order by a.return_sale_order_no ".$limit;
		$list = $this->db->query($sql);
		foreach ($list as &$value) {	
			unset($value['create_time'],$value['update_time']);		
			$value['view_url'] 			= U('/ReturnSaleOrder/view/id/'.$value['m_id']);		
			$curr_money[$value['currency_id']] += $value['money'];	
			$total['sum_qn']			+= $value['sum_qn'];	
		}	
		$list 							= _formatList($list);
		$list['total']					= array_merge($list['total'],_formatArray($total));
		$list['total']['title'] 		= $title;
		$list['total']['product_no']	= $list['list'][0]['product_no'];	
		$list['total']['product_name']	= $list['list'][0]['product_name'];	
		// 总合计
		$sql	= " select sum(quantity*capability*dozen*price*discount) as money, currency_id 
					from  return_sale_order a inner join  return_sale_order_detail b on a.id=b.return_sale_order_id 
					where b.product_id=".$id." and b.is_use=".$_GET['is_use']."
					group by currency_id ";
		$total	= M()->cache()->query($sql);	
		$currency=S('currency');
		foreach ($total as $t_list) {
			$all_money.=$currency[$t_list['currency_id']]['currency_no'].'：'.moneyFormat($t_list['money'], 0, C('MONEY_LENGTH')).'<br>';			
		}
		foreach ($curr_money as $k => $m) {
			$total_money.=$currency[$k]['currency_no'].'：'.moneyFormat($m, 0, C('MONEY_LENGTH')).'<br>';			
		}
		$list['total']['all_quantity']  = moneyFormat($count['quantity'], 0, 0);
		$list['total']['all_sum_qn']	= moneyFormat($count['sum_qn'],0,0);
		$list['total']['all_money']  	= $all_money;
		$list['total']['total_money']  	= $total_money;
		$list['page']					= $page;
		return $list;
	}
	
	/// 期初明细
	public function getInit($id) {				
		$sql	= "select *,a.id as m_id,sum(quantity*capability*dozen) as quantity,sum(quantity) sum_qn,
					sum(quantity*capability*dozen*price) as money, 
					sum(quantity*capability*dozen*price)/sum(quantity*capability*dozen) as avg_price
					from  init_storage a inner join  init_storage_detail b on a.id=b.init_storage_id 
					where b.product_id=".$id."
					group by a.id 
					order by a.id ";
		$list = $this->db->query($sql);
		foreach ($list as &$value) {			
			$value['view_url'] 			= U('/InitStorage/view/id/'.$value['m_id']);
			$total['sum_qn']			+= $value['sum_qn'];			
		}	
		$total							= _formatArray($total);
		$list 							= _formatList($list);		
		$list['total']['product_no']	= $list['list'][0]['product_no'];	
		$list['total']['product_name']	= $list['list'][0]['product_name'];	
		$list['total']					= array_merge($list['total'],$total);		
		$list['page']					= '';
		return $list;
	}
	
	/// 调整明细
	public function getAdjust($id) {		
		if ($_GET['add'] ==1) {
			$where 		=' and a.quantity >0';
			$prof_where = ' and (stocktake_quantity-storage_quantity)>0';
			$title  	= L('increase');
		}else {
			$where 		=' and a.quantity <0';
			$prof_where	= ' and (stocktake_quantity-storage_quantity)<0';
			$title  	= L('reduce');
		}
		$sql = " select count(*) as count,sum(sum_quantity) as quantity,sum(money) as money,sum(sum_qn) sum_qn from (
					 select b.id as main_id,b.adjust_no,sum(quantity) sum_qn,
					 abs(sum(quantity*capability*dozen)) as sum_quantity,
					 abs(sum(quantity*capability*dozen*price)) as money
				 	 from adjust_detail  a inner join adjust b on a.adjust_id= b.id
				  	 where  a.product_id=".$id.$where."
				  	 group by b.id
				  	 union all
				  	 select b.id as main_id,b.profitandloss_no as adjust_no,sum(stocktake_quantity) sum_qn,
				  	 	abs(sum((stocktake_quantity-storage_quantity)*capability*dozen)) as sum_quantity,
						abs(sum((stocktake_quantity-storage_quantity)*capability*dozen*price)) as money
				  	 from profitandloss_detail  a inner join profitandloss b on a.profitandloss_id= b.id
				  	 where b.state=2 and a.product_id=".$id.$prof_where."	
				  	 group by b.id	  
				  ) as temp group by 1=1 ";		
		$count 	= M()->cache()->query($sql);
		$count	= reset($count);			
		if (!$count) {
			$list['total']['title'] = $title;
			return ;
		}
		$p 		= new Page($count['count']);
		$page 	= $p->show();
		// 分页信息
		$limit 	= ' limit '.$p->firstRow . ','.$p->listRows;		
		$sql = " select * from (
					 select 1 as type,product_id,currency_id,b.id as main_id,adjust_date,b.adjust_no,sum(quantity) sum_qn,
					 	abs(sum(quantity*capability*dozen)) as sum_quantity,
						abs(sum(quantity*capability*dozen*price)) as money
				 	 from adjust_detail  a inner join adjust b on a.adjust_id= b.id
				  	 where  a.product_id=".$id.$where."
				  	 group by a.id
				  	 union all
				  	 select 2 as type,product_id,currency_id,b.id as main_id,profitandloss_date,b.profitandloss_no as adjust_no,sum(stocktake_quantity) sum_qn,
				  	 	abs(sum((stocktake_quantity-storage_quantity)*capability*dozen)) as sum_quantity,
					 	abs(sum((stocktake_quantity-storage_quantity)*capability*dozen*price)) as money
				  	 from profitandloss_detail  a inner join profitandloss b on a.profitandloss_id= b.id
				  	 where b.state=2 and a.product_id=".$id.$prof_where."	
				  	 group by a.id	  
				  ) as temp order by adjust_no asc ".$limit;		
		$list = $this->db->query($sql);
		foreach ($list as &$value) {	
			$value['sum_quantity']		= abs($value['sum_quantity']);	
			if ($value['type'] ==1 ) { // 调整
				$value['view_url'] 		= 'Adjust/view';		
			} else { // 盈亏
				$value['view_url'] 		= 'Profitandloss/view';		
			}
			
			$total['sum_qn']			+= $value['sum_qn'];			
		}	
		$list 							= _formatList($list);
		$list['total']					= array_merge($list['total'],_formatArray($total));
		$list['total']['title'] 		= $title;
		$list['total']['product_no']	= $list['list'][0]['product_no'];	
		$list['total']['product_name']	= $list['list'][0]['product_name'];	
		$list['total']['all_quantity']  = moneyFormat(abs($count['quantity']), 0, 0);
		$list['total']['all_sum_qn']	= moneyFormat($count['sum_qn'],0,0);
		$list['total']['all_money']  	= moneyFormat($count['money'], 0, C('MONEY_LENGTH'));
		$list['page']					= $page;
		return $list;
	}
	
	/// 进货单价走势
	public function getPriceForChart($id,$type = 'instock') {
		$from_date 	= date('Y-m-d', mktime(0,0,0,date('m')-2,'01',date('Y')));
		$to_date   	= date('Y-m-d', mktime(0,0,0,date('m'),date('t'),date('Y')));		
		if ($type == 'sale') { // 销售单价
			if (C('FLOW.DELIVERY')) { // 有发货流程
				$sql		= " select *,a.currency_id as in_currency_id,
							sum(if(a.sale_order_state=3, c.quantity*c.capability*c.dozen*c.pieces, b.quantity*b.capability*b.dozen*b.pieces)) as sum_quantity,
							sum(if(a.sale_order_state=3, c.quantity*c.capability*c.dozen*c.pieces*c.price*c.discount, b.quantity*b.capability*b.dozen*b.pieces*b.price*b.discount)) as money							
							from sale_order a inner join sale_order_detail b on a.id=b.sale_order_id
							left join delivery_detail c on b.id=c.sale_order_detail_id
							where order_date>='".$from_date."' and order_date<='".$to_date."'
							and b.product_id=".$id." and (a.sale_order_state=1 or (a.sale_order_state=3 and c.id >0))
							group by a.id";
			} else {	// 无发货流程
				$sql		= " select *,a.currency_id as in_currency_id,
							sum(quantity*capability*dozen*pieces) as sum_quantity,
							sum(quantity*capability*dozen*pieces*price*discount) as money
							from sale_order a inner join sale_order_detail b on a.id=b.sale_order_id
							where order_date>='".$from_date."' and order_date<='".$to_date."'
							and b.product_id=".$id."
							group by a.id";
			}			
		} else {
			$sql		= " select *,b.currency_id as in_currency_id,
							(quantity*capability*dozen*pieces) as sum_quantity,
							(quantity*capability*dozen*pieces*price) as money
						  	from instock a inner join instock_detail b on a.id=b.instock_id
							where real_arrive_date>='".$from_date."' and real_arrive_date<='".$to_date."'
							and b.product_id=".$id;	
		}	
		$rs				= $this->db->query($sql);		
		$date_format 	= C('DIGITAL_FORMAT') == 'eur' ? 'm/Y' : 'Y-m';
		$show_date		= array(0 => date($date_format, mktime(0,0,0,date('m')-2,'01',date('Y'))),
								1 => date($date_format, mktime(0,0,0,date('m')-1,'01',date('Y'))),
								2 => date($date_format, time()));
		foreach ((array)$rs as $list) {				
			$date = date($date_format, $type == 'sale' ? strtotime($list['order_date']) : strtotime($list['real_arrive_date']));
			$new[$list['in_currency_id']][$date]['sum_quantity'] += $list['sum_quantity'];
			$new[$list['in_currency_id']][$date]['sum_price'] 	 += $list['money'];
		}			
		unset($list);		
		foreach ($show_date as $date) { // 日期
			$temp['instock_date'] = $date;
			foreach ((array)$new as $c => $d_list) { // 币种
				$avg_price = 0;
				foreach ($d_list as $d=> $m_list) { // 单价
					if ($d == $date) {
						$avg_price =  money_format($m_list['sum_price']/$m_list['sum_quantity'], C('PRICE_LENGTH'));
					}						
				}
				$temp['currency_id']	= $c;
				$temp['avg_price']		= $avg_price;	
				$list[] = $temp;			
			}			
		}							
		return $this->_formatListReport($list);		
	}
	

}